package com.oceansoft.core.jxkh.dao.impl;

import com.hui.platform.system.exception.HuiException;
import com.hui.platform.system.utility.StringUtil;
import com.oceansoft.core.common.util.PageUtil;
import com.oceansoft.core.common.util.Pagination;
import com.oceansoft.core.domain.SysUser;
import com.oceansoft.core.jxkh.dao.IEvaluationDao;
import com.oceansoft.core.jxkh.domain.Evaluation;
import com.oceansoft.core.jxkh.domain.MyMark;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * Created by Administrator on 2017/10/24.
 */
@Repository
public class EvaluationDaoImpl implements IEvaluationDao {


    @Resource
    private JdbcTemplate jdbcTemplate;


    @Override
    public List<Map<String, Object>> getStatisticsDept(String deptId) throws HuiException {
        String sql = "select deptname, count(1) userCount" +
                "  from (select t1.name, t1.deptid, t2.name as deptname" +
                "          from t_sys_user t1" +
                "          left join t_sys_dept t2 on t1.deptid = t2.deptid" +
                "         where t2.parentid =rpad( '" + deptId + "' ,12,'0') ) tt1" +
                " group by deptname ";
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> getStatisticsUser(String deptId) throws HuiException {
        //上月考评情况
        String sql = " select u.name DEPTNAME,case when e.totalscore is null then '0' else e.totalscore end USERCOUNT from t_sys_user u left join (select * from t_jxkh_evaluation  where months= subStr(to_char(add_months(trunc(sysdate),-1),'yyyymm'),5,2) ) e on u.policenum = e.policenum where u.deptid='" + deptId + "' ";
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public MyMark findMonthZpByDbid(int dbid) throws HuiException {
        String sql = "select m.*,t.checkYear year,t.checkNo month from t_pskh_mark_my m INNER join t_pskh_task t on m.taskId = t.dbid where m.dbid = ?";
        MyMark myMark = jdbcTemplate.queryForObject(sql,new Object[]{dbid},new RowMapper<MyMark>() {

            @Override
            public MyMark mapRow(ResultSet rs, int args) throws SQLException {
                MyMark mm = new MyMark();
                mm.setDbid(rs.getInt("DBID"));
                mm.setTaskId(rs.getInt("TASKID"));
                mm.setWorkLog(rs.getString("WORKLOG"));
                mm.setLevel1(rs.getString("LEVEL1"));
                mm.setLevel2(rs.getString("LEVEL2"));
                mm.setLevel3(rs.getString("LEVEL3"));
                mm.setLevel4(rs.getString("LEVEL4"));
                mm.setLevel5(rs.getString("LEVEL5"));
                mm.setVacate(rs.getInt("VACATE"));
                mm.setDelay(rs.getInt("DELAY"));
                mm.setBefore(rs.getInt("BEFORE"));
                mm.setLost(rs.getInt("LOST"));
                mm.setStatus(rs.getInt("STATUS"));
                mm.setYear(rs.getString("year"));
                mm.setMonth(rs.getInt("month"));
                return mm;
            }
        });
        return myMark;
    }

    @Override
    public void editMonthZp(MyMark myMark) throws HuiException {
        String sql = "update t_pskh_mark_my set workLog = ? , level1= ? , level2 = ? , level3 = ? , level4 = ? , level5 = ? , vacate = ? , delay = ? , before = ? , lost = ?,status='1'  where dbid = ?";
        jdbcTemplate.update(sql,myMark.getWorkLog(),myMark.getLevel1(),myMark.getLevel2(), myMark.getLevel3() ,myMark.getLevel4(),myMark.getLevel5(),myMark.getVacate(),myMark.getDelay(),myMark.getBefore(),myMark.getLost(),myMark.getDbid());
    }

    @Override
    public Pagination<MyMark> myEvaluation(HashMap<Object, Object> map) throws HuiException {

        int offset = Integer.parseInt(map.get("offset").toString());
        int limit = Integer.parseInt(map.get("limit").toString());
        String sql = "select m.*,t.CHECKNO month from t_pskh_mark_my m inner join t_pskh_task t on m.TASKID = t.dbid where m.BECHECKERID = ? order by t.CHECKNO";
        //我的自评，
        String count_sql = PageUtil.getCountSQL(sql.toString());
        System.out.println(count_sql);
        int _total = jdbcTemplate.queryForInt(count_sql,new Object[]{map.get("userId")});
        String page_sql = PageUtil.getPageSQL(sql.toString(), offset,limit);
        List<MyMark> list = jdbcTemplate.query(page_sql, new Object[]{map.get("userId")}, new RowMapper<MyMark>() {
            @Override
            public MyMark mapRow(ResultSet rs, int arg1) throws SQLException {
                MyMark mm = new MyMark();
                mm.setDbid(rs.getInt("DBID"));
                mm.setTaskId(rs.getInt("TASKID"));
                mm.setWorkLog(rs.getString("WORKLOG"));
                mm.setLevel1(rs.getString("LEVEL1"));
                mm.setLevel2(rs.getString("LEVEL2"));
                mm.setLevel3(rs.getString("LEVEL3"));
                mm.setLevel4(rs.getString("LEVEL4"));
                mm.setLevel5(rs.getString("LEVEL5"));
                mm.setVacate(rs.getInt("VACATE"));
                mm.setDelay(rs.getInt("DELAY"));
                mm.setBefore(rs.getInt("BEFORE"));
                mm.setLost(rs.getInt("LOST"));
                mm.setStatus(rs.getInt("STATUS"));
                mm.setMonth(rs.getInt("MONTH"));
                return mm;
            }
        });
        System.out.println(sql);
        return new Pagination(list,_total,limit, (offset/limit)+1);
    }

    @Override
    public void selfEvaluation(Evaluation evaluation) throws HuiException {
        jdbcTemplate.update("insert into T_JXKH_EVALUATION (DBID ," +
                        "  DEPTID      ," +
                        "  NAME        ," +
                        " POLICENUM     ," +
                        " TYPE          ," +
                        " UPDATETIME    ," +
                        " SELFLEVEL1    ," +
                        " SELFLEVEL2    ," +
                        " SELFLEVEL3    ," +
                        " SELFLEVEL4    ," +
                        " SELFLEVEL5    ," +
                        " CHARGEWORK    ," +
                        " SUMMARY       ," +
                        " LEAVE         ," +
                        " LATE          ," +
                        " EARLIER       ," +
                        " ABSENTEEISM   ," +
                        " MONTHS        ," +
                        " YEARS         ) values (" +
                        "seq_T_JXKH_EVALUATION.nextval,?,?,?,?,sysdate,?,?,?,?,?,?,?,?,?,?,?,?,?)", evaluation.getDeptid(), evaluation.getName(), evaluation.getPoliceNum(), evaluation.getType(),
                evaluation.getSelflevel1(), evaluation.getSelflevel2(), evaluation.getSelflevel3(), evaluation.getSelflevel4(), evaluation.getSelflevel5(),
                evaluation.getChargework(), evaluation.getSummary(), evaluation.getLeave(), evaluation.getLate(), evaluation.getEarlier(), evaluation.getAbsenteeism(), evaluation.getMonths(), evaluation.getYears());
    }

    @Override
    public void leaderEvaluation(Evaluation evaluation) throws HuiException {
        jdbcTemplate.update("update t_pskh_mark_item SET " +
                        "  MARKNOTE      =? ," +
                        "  UPDATETIME    =sysdate ," +
                        "  LEVEL1 =? ," +
                        "  LEVEL2 =? ," +
                        "  LEVEL3 =? , " +
                        "  LEVEL4 =? , " +
                        "  LEVEL5 =? , " +
                        "  SOCRE1 =? , " +
                        "  SOCRE2 =? , " +
                        "  SOCRE3 =? , " +
                        "  SOCRE4 =? , " +
                        "  SOCRE5 =? , " +
                        "  REMARK1       =? , " +
                        "  REMARK2       =? , " +
                        "  REMARK3       =? , " +
                        "  REMARK4       =? , " +
                        "  REMARK5       =? , " +
                        "  status       =?  where dbid = ? ", evaluation.getFeedback(), evaluation.getLdeaderlevel1(), evaluation.getLdeaderlevel2()
                , evaluation.getLdeaderlevel3(), evaluation.getLdeaderlevel4(), evaluation.getLdeaderlevel5(), evaluation.getLdeadersocre1(), evaluation.getLdeadersocre2()
                , evaluation.getLdeadersocre3(), evaluation.getLdeadersocre4(), evaluation.getLdeadersocre5(), evaluation.getRemark1(), evaluation.getRemark2()
                , evaluation.getRemark3(), evaluation.getRemark4(), evaluation.getRemark5(),evaluation.getStatus(), evaluation.getDbid());
    }

    @Override
    public List<Evaluation> leaderElist(HashMap<String, String> map, SysUser user) throws Exception {
        String sql = "select * from v_jxkh_evaluation t" +
                " where checkerid = '" + user.getDbid() + "' ";
        //1季度测评 0月度测评
        if (map.containsKey("type") && StringUtil.isNotNullAndBlank(map.get("type"))) {
            sql += " and type = '" + map.get("type") + "' ";
        }
        sql += " order by selfstatus,status,updatetime desc ";
        System.out.println(sql);
        List<Evaluation> list = jdbcTemplate.query(sql, new RowMapper<Evaluation>() {
            @Override
            public Evaluation mapRow(ResultSet rs, int arg1) throws SQLException {
                Evaluation evaluation = new Evaluation();
                evaluation.setName(rs.getString("NAME"));
                evaluation.setDbid(rs.getInt("DBID"));
                evaluation.setSelflevel1(rs.getString("SELFLEVEL1"));
                evaluation.setSelflevel2(rs.getString("SELFLEVEL2"));
                evaluation.setSelflevel3(rs.getString("SELFLEVEL3"));
                evaluation.setSelflevel4(rs.getString("SELFLEVEL4"));
                evaluation.setSelflevel5(rs.getString("SELFLEVEL5"));
                evaluation.setLdeaderlevel1(rs.getString("LDEADERLEVEL1"));
                evaluation.setLdeaderlevel2(rs.getString("LDEADERLEVEL2"));
                evaluation.setLdeaderlevel3(rs.getString("LDEADERLEVEL3"));
                evaluation.setLdeaderlevel4(rs.getString("LDEADERLEVEL4"));
                evaluation.setLdeaderlevel5(rs.getString("LDEADERLEVEL5"));
                evaluation.setLdeadersocre1(rs.getString("LDEADERSOCRE1"));
                evaluation.setLdeadersocre2(rs.getString("LDEADERSOCRE2"));
                evaluation.setLdeadersocre3(rs.getString("LDEADERSOCRE3"));
                evaluation.setLdeadersocre4(rs.getString("LDEADERSOCRE4"));
                evaluation.setLdeadersocre5(rs.getString("LDEADERSOCRE5"));
                evaluation.setTotalscore(String.valueOf(rs.getInt("LDEADERSOCRE1") + rs.getInt("LDEADERSOCRE2") + rs.getInt("LDEADERSOCRE3") + rs.getInt("LDEADERSOCRE4") + rs.getInt("LDEADERSOCRE5")));
                evaluation.setPhoto(rs.getString("PHOTO"));
                evaluation.setStatus(rs.getString("STATUS"));
                return evaluation;
            }
        });
        return list;
    }

    @Override
    public void leaderConfirm(String dbid) throws HuiException {
        jdbcTemplate.update("update T_JXKH_EVALUATION set confirm ='1' where dbid = " + dbid);
    }

    @Override
    public Evaluation findEvaluation(int dbid) {
        String sql = "select * from v_jxkh_evaluation where dbid = " + dbid;
        List<Evaluation> list = jdbcTemplate.query(sql, new RowMapper<Evaluation>() {
            @Override
            public Evaluation mapRow(ResultSet rs, int arg1) throws SQLException {
                Evaluation evaluation = new Evaluation();
                evaluation.setName(rs.getString("NAME"));
                evaluation.setDbid(rs.getInt("DBID"));
//                evaluation.setChargework(rs.getString("CHARGEWORK"));
                evaluation.setSelflevel1(rs.getString("SELFLEVEL1"));
                evaluation.setSelflevel2(rs.getString("SELFLEVEL2"));
                evaluation.setSelflevel3(rs.getString("SELFLEVEL3"));
                evaluation.setSelflevel4(rs.getString("SELFLEVEL4"));
                evaluation.setSelflevel5(rs.getString("SELFLEVEL5"));
                evaluation.setLdeaderlevel1(rs.getString("LDEADERLEVEL1"));
                evaluation.setLdeaderlevel2(rs.getString("LDEADERLEVEL2"));
                evaluation.setLdeaderlevel3(rs.getString("LDEADERLEVEL3"));
                evaluation.setLdeaderlevel4(rs.getString("LDEADERLEVEL4"));
                evaluation.setLdeaderlevel5(rs.getString("LDEADERLEVEL5"));
                evaluation.setLdeadersocre1(rs.getString("LDEADERSOCRE1"));
                evaluation.setLdeadersocre2(rs.getString("LDEADERSOCRE2"));
                evaluation.setLdeadersocre3(rs.getString("LDEADERSOCRE3"));
                evaluation.setLdeadersocre4(rs.getString("LDEADERSOCRE4"));
                evaluation.setLdeadersocre5(rs.getString("LDEADERSOCRE5"));
                evaluation.setRemark1(rs.getString("REMARK1"));
                evaluation.setRemark2(rs.getString("REMARK2"));
                evaluation.setRemark3(rs.getString("REMARK3"));
                evaluation.setRemark4(rs.getString("REMARK4"));
                evaluation.setRemark5(rs.getString("REMARK5"));
                evaluation.setTotalscore(String.valueOf(rs.getInt("LDEADERSOCRE1") + rs.getInt("LDEADERSOCRE2") + rs.getInt("LDEADERSOCRE3") + rs.getInt("LDEADERSOCRE4") + rs.getInt("LDEADERSOCRE5")));
//                evaluation.setConfirm(rs.getString("CONFIRM"));
                evaluation.setMonths(rs.getString("MONTHS"));
                evaluation.setLeave(rs.getString("LEAVE"));
                evaluation.setLate(rs.getString("LATE"));
                evaluation.setEarlier(rs.getString("EARLIER"));
                evaluation.setAbsenteeism(rs.getString("ABSENTEEISM"));
                evaluation.setSummary(rs.getString("SUMMARY"));
                evaluation.setFeedback(rs.getString("FEEDBACK"));
                return evaluation;
            }
        });
        if (list.size() > 0) {
            return list.get(0);
        }
        return new Evaluation();
    }
}
